﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Core
{
    public abstract class DataProvider
    {
        public abstract DataTable Execute_Table(string strSQL);
        public abstract object Execute_Fill(string strSQL);
        public abstract int Execute_Modify(string SPName, SqlParameter[] para);

    }
    public class SQLProvider : DataProvider
    {
        public override DataTable Execute_Table(string strSQL)
        {
            using (SqlConnection testConnection = new SqlConnection(ConfigurationSettings.AppSettings["SQLProvider"]))
            {
                //try
                //{
                testConnection.Open();
                SqlCommand cmd = new SqlCommand(strSQL, testConnection);
                DataTable table = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(table);
                //table.Load(cmd.ExecuteReader());
                return table;
                //}
                //catch
                //{
                //    return null;
                //}
                //finally
                //{
                //    testConnection.Close();
                //}
            }
        }

        public override int Execute_Modify(string SPName, SqlParameter[] para)
        {
            using (SqlConnection testconnection = new SqlConnection(ConfigurationSettings.AppSettings["SQLProvider"]))
            {
                try
                {
                    testconnection.Open();
                    SqlCommand cmd = new SqlCommand(SPName, testconnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (para != null)
                    {
                        cmd.Parameters.AddRange(para);
                    }
                    int kq = (int)cmd.ExecuteNonQuery();
                    return kq;
                }
                catch
                {
                    return -1;
                }
                finally
                {
                    testconnection.Close();
                }
            }
        }
        public override object Execute_Fill(string strSQL)
        {
            using (SqlConnection testConnection = new SqlConnection(ConfigurationSettings.AppSettings["SQLProvider"]))
            {
                testConnection.Open();
                SqlCommand cmd = new SqlCommand(strSQL, testConnection);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }
}
